package Lab_v3;
import java.sql.*;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Hashtable;
import java.util.ArrayList;
 import java.io.*;
import java.sql.Array;

public class dbtables_handler {

   private Connection connection = null;
   private String database_name;
   protected String p_table_name;
   private String database_type;
   //private String database_name;
//    public static void main(String[] args) throws ClassNotFoundException

    public dbtables_handler(String set_database_type, String set_database_name) {
// load the sqlite-JDBC driver using the current class loader
//Class.forName("org.sqlite.JDBC");
database_name = set_database_name;
database_type = set_database_type;
//Connection connection = null;
        try
        {
// create a database connection
//connection = DriverManager.getConnection("jdbc:sqlite:sample.db");
            connection = DriverManager.getConnection("jdbc:" + database_type + ":" + database_name);
            Statement statement = connection.createStatement();
            //statement.setQueryTimeout(30); // set timeout to 30 sec.


        }
        catch (SQLException e)
        {
// if the error message is "out of memory",
// it probably means no database file is found
            System.err.println(e.getMessage());
        }
         finally {
            try {
                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException e) {
// connection close failed.
                System.err.println(e);
            }
        }

    }


      public void createMCQOptionTable() {
    String createString = "create table if not exists mcq_options " +
						"(mcq_id INTEGER PRIMARY KEY, " +
						"mcq_aplha_option varchar(40) NOT NULL, " +
						"option_value varchar(40) NOT NULL, " +
						"is_right BOOLEAN NOT NULL, "+
                                                "question_id INTEGER, " +
						"FOREIGN KEY(question_id) REFERENCES question_metadata(question_id)) ";
    Statement stmt = null;
    try {
      connection = DriverManager.getConnection("jdbc:" + database_type + ":" + database_name);
          stmt = connection.createStatement();
      //  stmt = connection.createStatement();
      stmt.executeUpdate("drop table if exists mcq_options");
      stmt.executeUpdate(createString);
          }
           catch (SQLException e)
        {
// if the error message is "out of memory",
// it probably means no database file is found
            System.err.println(e.getMessage());
        }
         finally {
            try {
                if ( stmt != null) {
                     stmt.close();
                }
            } catch (SQLException e) {
// connection close failed.
                System.err.println(e);
            }
        }
  }

   public void createQuestionMetadataTable() {
    String createString = "create table if not exists question_metadata " +
						"(question_id INTEGER PRIMARY KEY, " +
                                                "question_number integer NOT NULL, " +
						"question_string varchar(40) NOT NULL, " +
                                                       "test_id INTEGER NOT NULL, " +
						"FOREIGN KEY(test_id) REFERENCES test_metadata(test_id)) ";
    Statement stmt = null;
    try {
            connection = DriverManager.getConnection("jdbc:" + database_type + ":" + database_name);
        
        stmt = connection.createStatement();
      stmt.executeUpdate("drop table if exists question_metadata");
      stmt.executeUpdate(createString);
    }
  catch (SQLException e)
        {
// if the error message is "out of memory",
// it probably means no database file is found
            System.err.println(e.getMessage());
        }
         finally {
            try {
                if ( stmt != null) {
                     stmt.close();
                }
            } catch (SQLException e) {
// connection close failed.
                System.err.println(e);
            }
        }
  }

     public void createTestMetaData()  {
    String createString = "create table if not exists test_metadata "
            + "(test_id INTEGER PRIMARY KEY, "
            + "test_name varchar(40) NOT NULL, "
            + "test_description varchar(80) NOT NULL)";
    Statement stmt = null;
    try {
          connection = DriverManager.getConnection("jdbc:" + database_type + ":" + database_name);
        stmt = connection.createStatement();
       stmt.executeUpdate("drop table if exists test_metadata");
      stmt.executeUpdate(createString);

    }
   catch (SQLException e)
        {
// if the error message is "out of memory",
// it probably means no database file is found
            System.err.println(e.getMessage());
        }
         finally {
            try {
                if ( stmt != null) {
                     stmt.close();
                }
            } catch (SQLException e) {
// connection close failed.
                System.err.println(e);
            }
        }
  }

      public void insertData(String s) {
         Statement stmt = null;
             //System.out.println(p_table_name);
          try
        {
// create a database connection
//connection = DriverManager.getConnection("jdbc:sqlite:sample.db");
connection = DriverManager.getConnection("jdbc:" + database_type + ":" + database_name);
          stmt = connection.createStatement();
          
stmt.executeUpdate("insert into "+p_table_name+" values ("+s+")");

        }
        catch (SQLException e)
        {
// if the error message is "out of memory",
// it probably means no database file is found
            System.err.println(e.getMessage());
        }
         finally {
            try {
                if ( stmt != null) {
                     stmt.close();
                }
            } catch (SQLException e) {
// connection close failed.
                System.err.println(e);
            }
        }
//fetchAllData ();
      }

      protected void fetchAndViewAllData (String feildColumns[])
    {
    Statement stmt = null;
          try
        {
// create a database connection
connection = DriverManager.getConnection("jdbc:" + database_type + ":" + database_name);

          stmt = connection.createStatement();
  ResultSet rs = stmt.executeQuery("select * from "+p_table_name);
            while (rs.next())
            {
for (int i = 0; i < feildColumns.length; i++) {
    System.out.println(feildColumns[i] +" =  "+ rs.getString(feildColumns[i]));

}

            }
        }
        catch (SQLException e)
        {
// if the error message is "out of memory",
// it probably means no database file is found
            System.err.println(e.getMessage());
        }
         finally {
            try {
                if ( stmt != null) {
                     stmt.close();
                }
            } catch (SQLException e) {
// connection close failed.
                System.err.println(e);
            }
        }
    }

protected Hashtable fetchSingleRow (String searchfeildColumn,String searchfieldValue,String feildColumns[])
    {
    Statement stmt = null;
   Hashtable object_hastable = new Hashtable();
  
          try
        {
// create a database connection
connection = DriverManager.getConnection("jdbc:" + database_type + ":" + database_name);

          stmt = connection.createStatement();
  ResultSet rs = stmt.executeQuery("select * from "+p_table_name+" where "+searchfeildColumn+" =  '"+searchfieldValue+"'");
            while (rs.next())
            {
                for (int i = 0; i < feildColumns.length; i++) {
    object_hastable.put(feildColumns[i], rs.getString(feildColumns[i]));

}

            }

        }
        catch (SQLException e)
        {
// if the error message is "out of memory",
// it probably means no database file is found
            System.err.println(e.getMessage());
        }
         finally {
            try {
                if ( stmt != null) {
                     stmt.close();
                }
            } catch (SQLException e) {
// connection close failed.
                System.err.println(e);
            }
        }
           return object_hastable;
    }



protected Hashtable getRightOptions(String test_id)
    {
     Statement stmt = null;
   Hashtable object_hastable = new Hashtable();

          try
        {
// create a database connection
connection = DriverManager.getConnection("jdbc:" + database_type + ":" + database_name);

          stmt = connection.createStatement();
          ResultSet rs;
//            if (searchfeildColumn == null || searchfieldValue == null) {
//                 rs = stmt.executeQuery("select * from " + p_table_name);
//            } else {
                rs = stmt.executeQuery("select a.mcq_aplha_option, b.question_id from mcq_options a, question_metadata b  where a.question_id=b.question_id and b.test_id = '1' and a.is_right= '1' order by a.question_id asc");
           // }
  ArrayList arrayList = new ArrayList();
            while (rs.next())
            {
//);
//                for (int i = 0; i < feildColumns.length; i++) {
//
//
//
//
//}
    // arrayList = new ArrayList();
                   int a = object_hastable.size();
object_hastable.put((a+1), rs.getString("mcq_aplha_option"));
    //arrayList.add(rs.getString("option_value"));
  //  int a = object_hastable.size();

 //arrayList = new ArrayList();

            }

        }
        catch (SQLException e)
        {
// if the error message is "out of memory",
// it probably means no database file is found
            System.err.println(e.getMessage());
        }
         finally {
            try {
                if ( stmt != null) {
                     stmt.close();
                }
            } catch (SQLException e) {
// connection close failed.
                System.err.println(e);
            }
        }

         return object_hastable;
}
protected Hashtable fetchAllRows (String searchfeildColumn,String searchfieldValue,String feildColumns[])
    {
    Statement stmt = null;
   Hashtable object_hastable = new Hashtable();

          try
        {
// create a database connection
connection = DriverManager.getConnection("jdbc:" + database_type + ":" + database_name);

          stmt = connection.createStatement();
          ResultSet rs;
            if (searchfeildColumn == null || searchfieldValue == null) {
                 rs = stmt.executeQuery("select * from " + p_table_name);
            } else {
                rs = stmt.executeQuery("select * from " + p_table_name + " where " + searchfeildColumn + " =  '" + searchfieldValue + "'");
            }

            while (rs.next())
            {
 ArrayList arrayList = new ArrayList();
                for (int i = 0; i < feildColumns.length; i++) {

                    arrayList.add(rs.getString(feildColumns[i]));
 

}
int a = object_hastable.size();
object_hastable.put((a+1), arrayList);
            }

        }
        catch (SQLException e)
        {
// if the error message is "out of memory",
// it probably means no database file is found
            System.err.println(e.getMessage());
        }
         finally {
            try {
                if ( stmt != null) {
                     stmt.close();
                }
            } catch (SQLException e) {
// connection close failed.
                System.err.println(e);
            }
        }
     
         return object_hastable;
    }


    protected Hashtable getSingleQuestionsForTest(String test_id, String question_number) {
        Statement stmt = null;
           Hashtable object_hastable = new Hashtable();
        ArrayList parentArrayList = new ArrayList();
    
        try {

            connection = DriverManager.getConnection("jdbc:" + database_type + ":" + database_name);

            stmt = connection.createStatement();
            ResultSet rs;

            rs = stmt.executeQuery("select a.question_number, a.question_string, b.option_value from question_metadata a, mcq_options b where a.test_id = '" + test_id + "' and a.question_number = '" + question_number + "' and a.question_id = b.question_id order by a.question_number asc");

           ArrayList arrayList = new ArrayList();
            while (rs.next()) {
                
                String question_string = rs.getString("question_string");
               // System.out.println(question_string);
                if (!parentArrayList.contains(question_string) || parentArrayList.isEmpty()) {
                    if (!arrayList.isEmpty()) {
                       // arrayList = new ArrayList();
                     //  int a = object_hastable.size();
                      //  object_hastable.put(question_number, arrayList);
                    }
                    object_hastable.put("question_number", rs.getString("question_number"));
                    int a = object_hastable.size();
                    object_hastable.put("question_string", rs.getString("question_string"));
                    object_hastable.put(a, rs.getString("option_value"));
                    parentArrayList.add(rs.getString("question_number"));
                    parentArrayList.add(rs.getString("question_string"));
                    parentArrayList.add(rs.getString("option_value"));
                    arrayList.add(rs.getString("question_number"));
                    arrayList.add(rs.getString("question_string"));
                    arrayList.add(rs.getString("option_value"));



                } else {
int a = object_hastable.size();
                    object_hastable.put(a, rs.getString("option_value"));
                    parentArrayList.add(rs.getString("option_value"));
                   arrayList.add(rs.getString("option_value"));
                }
            //int a = object_hastable.size();
           // object_hastable.put((a+1), arrayList);
            }


        } catch (SQLException e) {

            System.err.println(e.getMessage());
        } finally {
            try {
                if (stmt != null) {
                    stmt.close();
                }
            } catch (SQLException e) {
// connection close failed.
                System.err.println(e);
            }
        }

        return object_hastable;
    }



    }


